package model;

import init.DBManagement;

import java.util.ArrayList;
import java.util.Map;

import model.beans.ObservationBean;
import model.beans.SubtopicBean;
import model.beans.TopicBean;
import model.beans.UnidadBean;

public class SubjectContent {
	private static String str_format = "yyyy/MM/dd";
	
	public static boolean existsDates( int int_subtopicId ){
		boolean bol_retVal = false;
		String str_exists = 
			" select subtema_tk "+
			" from plasmar "+
			" where subtema_tk = "+int_subtopicId;
		DBManagement database = new DBManagement();
		database.executeQuery( str_exists );
		if( database.getNumRows()>0 ){
			bol_retVal = true;
		}
		database = null;
		return bol_retVal;
	}
	
	public static ArrayList list(String str_materiatk){
		int lastUnitId = -1;
		int lastTopicId = -1;
		int unitId = 0;
		int topicId=0;
		int subtopicId=0;
		String tempTopicId = null;
		String tempSubtopicId = null;
		UnidadBean unidad = null;    	
		TopicBean topic = null;
		SubtopicBean subtopic = null;
		ArrayList arrL_units = new ArrayList();		
		ArrayList arrL_topics = new ArrayList();
		ArrayList arrL_subtopics = new ArrayList();

		String str_list =
			" SELECT  unidad.NUMERO,      " +
			"        unidad.DESCRIPCION ,      " +
			"        tema.NUMERO,                  " +
			"        tema.DESCRIPCION,          " +
			"        subtema.NUMERO,            " +
			"        subtema.DESCRIPCION,     " +
			"        subtema.STE_TECH_KEY     " +
			"  FROM PLAMSTB materia,           " +
			"  PLAUNIR unidad                          " +
			" LEFT JOIN PLATEMR tema            " +
			" ON unidad.PLU_TECH_KEY = tema.UNIDAD_TK "+
			" LEFT JOIN PLASTER subtema   " +
			" ON subtema.TEMA_TK = tema.TEM_TECH_KEY " +
			" WHERE unidad.PLAN_TK = materia.PLA_TECH_KEY " +
			"AND materia.MATERIA_TK = '" + str_materiatk + "'" +
			" ORDER BY 1, 3, 5";
		
		DBManagement database = new DBManagement();
		database.executeQuery( str_list );

		for( int int_i = 0 ; int_i < database.getNumRows(); int_i++ ){
				unitId = Integer.parseInt(database.getItem(int_i, 0));
				if(lastUnitId != unitId){
						lastUnitId = unitId;
						unidad = new UnidadBean();
						unidad.setInt_unitNumber(unitId);
						unidad.setStr_descripcion(database.getItem(int_i, 1));
						arrL_units.add(unidad);
						
						tempTopicId = database.getItem(int_i, 2);
						if(tempTopicId != null){
								topicId = Integer.parseInt(tempTopicId);
								lastTopicId = topicId;
								topic = new TopicBean();
								topic.setInt_topicNumber(topicId);
								topic.setStr_descripcion(database.getItem(int_i, 3));
								arrL_topics = unidad.getArrL_topic();
								arrL_topics.add(topic);
								unidad.setArrL_topic(arrL_topics);
								
								tempSubtopicId = database.getItem(int_i, 4);
								if(tempSubtopicId != null){
										subtopicId = Integer.parseInt(tempSubtopicId);
										subtopic = new SubtopicBean();
										subtopic.setInt_subtopicNumber(subtopicId);
										subtopic.setStr_descripcion(database.getItem(int_i, 5));
										subtopic.setInt_subtopicId((Integer.parseInt(database.getItem(int_i, 6))));
										arrL_subtopics = topic.getArrL_subtopic();
										arrL_subtopics.add(subtopic);
										topic.setArrL_subtopic(arrL_subtopics);
								}
						}
				}else{
						tempTopicId = database.getItem(int_i, 2);
						if(tempTopicId != null){
								topicId = Integer.parseInt(tempTopicId);
								if(lastTopicId != topicId){
										lastTopicId = topicId;
										topic = new TopicBean();
										topic.setInt_topicNumber(topicId);
										topic.setStr_descripcion(database.getItem(int_i, 3));
										arrL_topics = unidad.getArrL_topic();
										arrL_topics.add(topic);
										unidad.setArrL_topic(arrL_topics);
										
										tempSubtopicId = database.getItem(int_i, 4);
										if(tempSubtopicId != null){
												subtopicId = Integer.parseInt(tempSubtopicId);
												subtopic = new SubtopicBean();
												subtopic.setInt_subtopicNumber(subtopicId);
												subtopic.setStr_descripcion(database.getItem(int_i, 5));
												subtopic.setInt_subtopicId((Integer.parseInt(database.getItem(int_i, 6))));
												arrL_subtopics = topic.getArrL_subtopic();
												arrL_subtopics.add(subtopic);
												topic.setArrL_subtopic(arrL_subtopics);
										}
								}else{
										subtopicId = Integer.parseInt(database.getItem(int_i, 4));
										subtopic = new SubtopicBean();
										subtopic.setInt_subtopicNumber(subtopicId);
										subtopic.setStr_descripcion(database.getItem(int_i, 5));
										subtopic.setInt_subtopicId((Integer.parseInt(database.getItem(int_i, 6))));
										arrL_subtopics = topic.getArrL_subtopic();
										arrL_subtopics.add(subtopic);
										topic.setArrL_subtopic(arrL_subtopics);
								}
						}					
				}
		}	
		return arrL_units;
	}
	public static void getDates(String str_materiatk, String str_idMaestro, ArrayList arrL_units){

		UnidadBean unit = null;
		TopicBean topic = null;
		SubtopicBean subtopic = null;
		
		boolean notUnit;
		boolean notTopic;
		boolean notSubtopic;
		
		int cont = 0;
		int int_contUnit = 0;
		int int_contTopic = 0;
		int int_contSubtopic = 0;
		
		int int_unitId = 0;
		int int_topicId = 0;
		int int_subtopicId = 0;
		
		ArrayList arrL_topics = null;
		ArrayList arrL_subtopics = null;
		
		String str_list =
			" SELECT  unidad.NUMERO,      " +		
			"        tema.NUMERO,                  " +		
			"        subtema.NUMERO,             " +
			"         to_char( FECHA_PLANEADA, '" + str_format + "' ) AS FECHA_PLANEADA,  " + 
			"         to_char( FECHA_IMPARTIDA, '" + str_format + "' ) AS FECHA_IMPARTIDA  " + 
			" FROM PLAMSTB materia,             " +
			" PLAUNIR unidad                       "	+
			" INNER JOIN PLATEMR tema            " +
			" ON unidad.PLU_TECH_KEY = tema.UNIDAD_TK "+
			" INNER JOIN PLASTER subtema   " +
			" ON subtema.TEMA_TK = tema.TEM_TECH_KEY " +
			" INNER JOIN PLASMAR sm " +
			" ON sm.SUBTEMA_TK = subtema.STE_TECH_KEY AND " +
			" sm.MAESTRO_TK = '" + str_idMaestro +"'" +
			" WHERE unidad.PLAN_TK = materia.PLA_TECH_KEY "  +
			" AND materia.MATERIA_TK = '" + str_materiatk + "'" +
			" ORDER BY 1, 3, 5";
		
		DBManagement database = new DBManagement();
		database.executeQuery( str_list );
		cont = database.getNumRows();
		
			for( int int_i = 0 ; int_i <cont; int_i++ ){
					notUnit = true;
					notTopic = true;
					notSubtopic = true;
					int_unitId = Integer.parseInt(database.getItem(int_i, 0));
					while(notUnit && (arrL_units.size() >= int_contUnit)){
							unit = (UnidadBean)arrL_units.get(int_contUnit++);
							if(unit.getInt_unitNumber() == int_unitId){
									notUnit = false;
									int_contUnit = 0;
									arrL_topics = unit.getArrL_topic();
									int_topicId = Integer.parseInt(database.getItem(int_i, 1));
									while(notTopic && (arrL_topics.size() >= int_contTopic)){
											topic = (TopicBean)arrL_topics.get(int_contTopic++);
											if(topic.getInt_topicNumber() == int_topicId){
													notTopic = false;
													int_contTopic = 0;
													arrL_subtopics = topic.getArrL_subtopic();
													int_subtopicId = Integer.parseInt(database.getItem(int_i, 2));
													while(notSubtopic && (arrL_subtopics.size() >= int_contSubtopic)){
															subtopic = (SubtopicBean)arrL_subtopics.get(int_contSubtopic++);
															if(subtopic.getInt_subtopicNumber() == int_subtopicId){
																	notSubtopic = false;
																	int_contSubtopic = 0;
																	subtopic.setStr_fechaPlaneada(database.getItem(int_i, 3));																	
																	subtopic.setStr_fechaImpartida(database.getItem(int_i, 4));																	
															}			
													}
											}
									}
							}		
					}			
			}		
	}
	
	public static int saveDate(Map mapa, String userId, String type){
		int int_subtemaId = 0;	
		String tipoPlaneada="PLANNED";
		String tipoImpartida="ACTUAL";
		
		String str_fechaPlaneada = "";
		String str_fechaImpartida = "";
		int size = mapa.size()/2;
		boolean querySuccess = false;
		int ret_val = 0;
		
		DBManagement database = new DBManagement();
		if(type.equals(tipoPlaneada)){
				
		}
		
		if(type.equals(tipoPlaneada)){
				for(int i =0; i<size; i++){
						str_fechaPlaneada = (String)mapa.get("str_plannedDate" + i);
						int_subtemaId = Integer.parseInt((String)mapa.get("subtopic" + i));
						querySuccess = database.executeQuery( 
								"SELECT FECHA_PLANEADA " +
								"FROM PLASMAR " +
								"WHERE SUBTEMA_TK = " + int_subtemaId +" "+
								"AND MAESTRO_TK = '" + userId + "'");
						if(querySuccess){
								if(database.getNumRows()==1){
										database.executeQuery(
												"UPDATE PLASMAR "+
												"SET FECHA_PLANEADA= to_date( '"+str_fechaPlaneada+"', 'yyyy/MM/dd') " +
												"WHERE SUBTEMA_TK=" +int_subtemaId+
												" AND MAESTRO_TK='" +userId+"'");								
								}else{
									  	database.executeQuery(
									  			"INSERT INTO PLASMAR ("+
												"SUBTEMA_TK, " +
												"MAESTRO_TK,  " +
												"FECHA_PLANEADA, " +
												"FECHA_IMPARTIDA " +
												 " ) VALUES (  "+
												 int_subtemaId + ", " +
												 "'" + userId +"',  " +
												 "to_date( '"+str_fechaPlaneada+"', 'yyyy/MM/dd'),       " + 
												 "to_date( '"+str_fechaImpartida+"', 'yyyy/MM/dd'))       ");									  	
								}
								ret_val = 1;
						}
						
				}
		}else if(type.equals(tipoImpartida)){
				for(int i =0; i<size; i++){
						str_fechaImpartida = (String)mapa.get("str_actualDate" + i);
						int_subtemaId = Integer.parseInt((String)mapa.get("subtopic" + i));
						querySuccess = database.executeQuery( 
								"SELECT FECHA_IMPARTIDA " +
								"FROM PLASMAR " +
								"WHERE SUBTEMA_TK = " + int_subtemaId +" "+
								"AND MAESTRO_TK = '" + userId + "'");
						if(querySuccess){
								if(database.getNumRows()==1){
										database.executeQuery(
												"UPDATE PLASMAR "+
												"SET FECHA_IMPARTIDA= to_date( '"+str_fechaImpartida+"', 'yyyy/MM/dd') " +
												"WHERE SUBTEMA_TK=" +int_subtemaId+
												" AND MAESTRO_TK='" +userId+"'");														
								}else{
								  	database.executeQuery(
								  			"INSERT INTO PLASMAR ("+
											"SUBTEMA_TK, " +
											"MAESTRO_TK,  " +
											"FECHA_PLANEADA, " +
											"FECHA_IMPARTIDA " +
											 " ) VALUES (  "+
											 int_subtemaId + ", " +
											 "'" + userId +"',  " +
											 "to_date( '"+str_fechaPlaneada+"', 'yyyy/MM/dd'),       " + 
											 "to_date( '"+str_fechaImpartida+"', 'yyyy/MM/dd'))       ");									  	
								}
								ret_val = 1;
						}
				}
		}
		
		return ret_val;
	}
	
	public static ObservationBean obsInstance(String materia, String alumnoId, String maestroId){
		ObservationBean bn_obs = new ObservationBean();
		String str_list =
			" SELECT DISTINCT     " +
			"       OBS_TECH_KEY ,      " +
			"       OBSERVACIONES       " +
			" FROM PLAOBSR               " +
			" WHERE MATERIA_TK = '" + materia + "' " +
			" AND ALUMNO_TK = '" + alumnoId + "' " +
			" AND MAESTRO_TK = '" + maestroId + "'";
		DBManagement database = new DBManagement();
		database.executeQuery(str_list);
		if(database.getNumRows()>0){
			bn_obs.setInt_observationId(Integer.parseInt(database.getItem(0, 0)));
			bn_obs.setStr_descripcion(database.getItem(0, 1));
		}else{
			bn_obs.setInt_observationId(0);
			bn_obs.setStr_descripcion("");
		}
		bn_obs.setStr_idAlumno(alumnoId);
		bn_obs.setStr_idMateria(materia);
		bn_obs.setStr_idMaestro(maestroId);
		
		return bn_obs;
	}
	public static int edit(Map map_data){
		int ret_val = 0;
		int int_idObservation = ((Integer)map_data.get("int_idObservation")).intValue();
		String str_descripcion = (String)map_data.get("str_descripcion");
		
		String str_update = "UPDATE PLAOBSR " +
		    " SET    OBSERVACIONES = '"+str_descripcion+ "'"+           
            " where  OBS_TECH_KEY = "+int_idObservation;

		DBManagement database = new DBManagement();
		 boolean bol_insertStatus = database.executeQuery( str_update );
		 ret_val = bol_insertStatus ? 1 : 0;
	     database = null;
		
		return ret_val;
	}
	
	public static int save(Map map_data){
		int ret_val = 0;

		String str_descripcion = (String)map_data.get("str_descripcion");
		String str_idMateria = (String)map_data.get("str_idMateria");
		String str_idAlumno = (String)map_data.get("str_idAlumno");
		String str_idMaestro = (String)map_data.get("str_idMaestro");
		
		String str_update = "INSERT INTO PLAOBSR( " +
			"		 OBSERVACIONES, "+		
            "        MATERIA_TK, "+	         
            "        ALUMNO_TK , "+
            "        MAESTRO_TK  " +
            "		  ) VALUES ( "+
            "'" + str_descripcion +"', "+
            "'" + str_idMateria +"', "+
            "'" + str_idAlumno+"', "+
            "'" + str_idMaestro +"'" + 
            " ) ";

		DBManagement database = new DBManagement();
		 boolean bol_insertStatus = database.executeQuery( str_update );
		 ret_val = bol_insertStatus ? 1 : 0;
	     database = null;
		
		return ret_val;
	}
}
